﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;


namespace IHISCETeim.Web.Export.FTradeModel
{
    public partial class FTradeExportRZZRKZJQRS : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "应收帐款_财务部融资租赁款结算确认书.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "融资租赁款结算确认书";
            }
        }

        protected string DateFrom
        {
            get
            {
                return GetRequestValue<string>("DateFrom", DateTime.Now.ToString("yyyy-MM-dd"));
                //DateTime dt = DateTime.Now; //当前时间
                //if (dt.Day >= 25)
                //    return DateTime.Now.ToString("yyyy-MM-25");
                //else
                //    return DateTime.Now.ToString("yyyy-MM-01");

               // return GetRequestValue<string>("DateFrom", DateTime.Now.ToString("yyyy-MM-01"));
            }
        }
        protected string DateTo
        {
            get
            {
                return GetRequestValue<string>("DateTo", DateTime.Now.ToString("yyyy-MM-dd"));
               // DateTime dt = DateTime.Now; //当前时间
               // return DateTime.Now.ToString("yyyy-MM-dd");
                //DateTime dt = Convert.ToDateTime(DateTime.Now.AddMonths(1).ToString("yyyy-MM-01")).AddDays(-1); //当前时间
                //return GetRequestValue<string>("DateTo", dt.ToString("yyyy-MM-dd")) + " 23:59:59";
            }
        }
        ///// <summary>
        ///// 当前日期
        ///// </summary>
        //protected string CurrDate
        //{
        //    get
        //    {
        //        DateTime dt = DateTime.Now; //当前时间
        //        if (dt.Day >= 25)
        //        { 
                    
        //        }
        //        return DateTime.Now.ToString("yyyy年MM月dd日");
        //    }
        //}

        protected void Page_Load(object sender, EventArgs e)
        {
            InitializeWorkbook();

            DataTableCollection dtc = FTradeReportController.FTradeExportGetRZZRKZJQRS(DateFrom, DateTo);
            DataTable sboagentlist = SBOController.GetAgentList();//所有经销商列表


            DataTable agentDT = dtc[0];
            DataTable BalanceDT = dtc[1];
            DataTable FXFinanceLeaseDT = dtc[2];//放款表
            //
            if (agentDT.Rows.Count <= 0)
            {
                Response.Write(string.Format("从{0}到{1}没有任何融资款录入,请确认", DateFrom, DateTo));
                Response.End();
                return;
            }




            DataTable agentBalanceDT = new DataTable();
            DataTable agentFXFinanceLeaseDT = new DataTable();

            int sheetindex = 1;//从第二个工作表开始
            foreach (DataRow dr in agentDT.Rows)
            {
                string AliasName = dr["JingXiaoShang"].ToString();
                string MoneyFrom = dr["MoneyFrom"].ToString();
                //经销商全称                
                DataRow[] drs = sboagentlist.Select(string.Format(" AliasName ='{0}'", AliasName.Trim()));
                string cardname = drs[0]["CardName"].ToString();

                //agentBalanceDT = BalanceDT.Select(" JingXiaoShang='" + AliasName + "' ").CopyToDataTable();

                //多经销商处理
                switch (AliasName.Trim())
                {
                    case "湖南金力源":
                        agentBalanceDT = BalanceDT.Select(" JingXiaoShang in('湖南金力源','湖南金土地') ").CopyToDataTable();//sqlcommonFiled = " JingXiaoShang in('湖南金力源','湖南金土地')";
                        break;
                    case "青岛金吉宇":
                       agentBalanceDT = BalanceDT.Select(" JingXiaoShang in('青岛金吉宇','青岛吉宇') ").CopyToDataTable();// sqlcommonFiled = " JingXiaoShang in('青岛金吉宇','青岛吉宇')";
                        break;
                    default:
                        agentBalanceDT=BalanceDT.Select(" JingXiaoShang='" + AliasName + "' ").CopyToDataTable();
                        break;
                }
                try
                {
                    //融资租赁款明细(进度表来源) 2014-02-19
                    switch (AliasName.Trim())
                    {
                        case "湖南金力源":
                            agentFXFinanceLeaseDT = FXFinanceLeaseDT.Select(" AliasName in('湖南金力源','湖南金土地') ").CopyToDataTable();//sqlcommonFiled = " JingXiaoShang in('湖南金力源','湖南金土地')";
                            break;
                        case "青岛金吉宇":
                            agentFXFinanceLeaseDT = FXFinanceLeaseDT.Select(" AliasName in('青岛金吉宇','青岛吉宇') ").CopyToDataTable();// sqlcommonFiled = " JingXiaoShang in('青岛金吉宇','青岛吉宇')";
                            break;
                        default:
                            agentFXFinanceLeaseDT = FXFinanceLeaseDT.Select(" AliasName='" + AliasName + "' ").CopyToDataTable();
                            break;
                    }

                }
                catch(Exception eeeee){
                    agentFXFinanceLeaseDT = new DataTable(); ;
                }
                ISheet sheet2 = hssfworkbook.CloneSheet(0);
                hssfworkbook.SetSheetName(sheetindex, string.Format("{0}{1}_{2}", GetRZFKDate().ToString("yyyy.MM.dd"), AliasName,sheetindex));
                //ISheet isExistSheet = hssfworkbook.GetSheet(AliasName);
                //if (isExistSheet == null)
                //{
                //    hssfworkbook.SetSheetName(sheetindex, string.Format("{0}{1}", GetRZFKDate().ToString("yyyy.MM.dd"), AliasName));
                //}
                //else
                //{
                //    hssfworkbook.SetSheetName(sheetindex, string.Format("{0}{1}", GetRZFKDate().ToString("yyyy.MM.dd"), AliasName+"1"));//存在第二个同名工作表
                //}

                WriteSheet(sheet2,agentFXFinanceLeaseDT, agentBalanceDT, cardname, MoneyFrom);
                sheet2.ForceFormulaRecalculation = true;
                sheetindex++;
            }
            
            ////隐藏
            hssfworkbook.RemoveSheetAt(0);
            //
            WriteToFile();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheet2"></param>
        /// <param name="agentFXFinanceLeaseDT">融资租赁款明细(进度表来源) 2014-02-19</param>
        /// <param name="agentBalanceDT"></param>
        /// <param name="cardname"></param>
        /// <param name="MoneyFrom"></param>
        private void WriteSheet(ISheet sheet2, DataTable agentFXFinanceLeaseDT, DataTable agentBalanceDT, string cardname, string MoneyFrom)
        {
            //文本处理
            sheet2.GetRow(2).GetCell(0).SetCellValue(string.Format("致：{0}", cardname));            
            sheet2.GetRow(32).GetCell(7).SetCellValue(string.Format("{0}", DateTime.Now.ToString("yyyy年MM月dd日")));
            sheet2.GetRow(5).GetCell(0).SetCellValue(string.Format("  根据贵我公司的约定，我公司将{0}收到的贵公司{2}融资租赁款结算情况列示如下,请予以核实确认。我司将于{1}前对应返还的款项进行结算。", GetRZFKDate().ToString("yyyy年MM月dd日"), GetRZCZDate().ToString("yyyy年MM月dd日"), MoneyFrom));
            string datetemp = "";
            if (GetRZFKDate().Day < 25)
                datetemp = GetRZFKDate().ToString("MM月20日");
            else
                datetemp = GetRZCZDate().ToString("MM月05日");

            sheet2.GetRow(26).GetCell(0).SetCellValue(string.Format("       请于{0}前确认回传,以上如有疑议，请和我司财务部联系(联系人：王晓娟，电话：0592-6292250,传真:0592-6292200). ", datetemp));
            //加入融资租赁款明细(进度表来源) 2014-02-19
            int rowindexBase = 9;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            int nextDTStratRow = rowindexBase;
            if (agentFXFinanceLeaseDT.Rows.Count > 4)
            {
                nextDTStratRow = rowindexBase + agentFXFinanceLeaseDT.Rows.Count +4;
                HSSFRow 源格式行 = (HSSFRow)sheet2.GetRow(rowReal);
                MyInsertRow((HSSFSheet)sheet2, rowindexBase + 1, agentFXFinanceLeaseDT.Rows.Count - 4, 源格式行);
            }
            else
                nextDTStratRow = 17;

            foreach (DataRow dr in agentFXFinanceLeaseDT.Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet2.GetRow(rowReal);
              
                row1.GetCell(0).SetCellValue(rowindex + 1);
                row1.GetCell(1).SetCellValue(dr["CustomerName"].ToString());
                row1.GetCell(2).SetCellValue(1);
                row1.GetCell(3).SetCellValue(dr["MachineType"].ToString());
                row1.GetCell(4).SetCellValue(dr["SerialNumber"].ToString());
                row1.GetCell(5).SetCellValue(Convert.ToDouble(dr["LTotalPrice"]));
                row1.GetCell(6).SetCellValue(Convert.ToDouble(dr["LAmountFinancing"]));
                row1.GetCell(7).SetCellValue(Convert.ToDouble(dr["LAmountFinancing"]));

                rowindex++;
                rowReal = rowindex + rowindexBase;
            }


            rowindexBase = nextDTStratRow;
            rowindex = 0;
            rowReal =  rowindex + rowindexBase;

            //处理明细
            if (agentBalanceDT.Rows.Count >5)
            {
                HSSFRow 源格式行 = (HSSFRow)sheet2.GetRow(rowReal);
                MyInsertRow((HSSFSheet)sheet2, rowindexBase+1, agentBalanceDT.Rows.Count - 5, 源格式行);
            }


            foreach (DataRow dr in agentBalanceDT.Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet2.GetRow(rowReal);
                //合并处理
                //sheet2.AddMergedRegion(new CellRangeAddress(rowReal, rowReal, 2, 4));

                row1.GetCell(0).SetCellValue(rowindex + 1);
                row1.GetCell(1).SetCellValue(dr["HeTongHao"].ToString());
                row1.GetCell(2).SetCellValue(dr["XingHao"].ToString());
                row1.GetCell(3).SetCellValue(dr["JiHao"].ToString());
                row1.GetCell(4).SetCellValue(Convert.ToDouble(dr["HeTongJinE"]));
                row1.GetCell(5).SetCellValue(Convert.ToDouble(dr["YiShouKuanXiang"]));
                row1.GetCell(6).SetCellValue(Convert.ToDouble(dr["BalanceMoney"]));
                row1.GetCell(7).SetCellFormula(string.Format("E{0}-F{0}-G{0}", rowReal+1));
                row1.GetCell(8).SetCellValue(dr["FuKuanTiaoKuan"].ToString());               

                rowindex++;
                rowReal = rowindex + rowindexBase;
            }
        }

        private DateTime GetRZFKDate()
        {
            DateTime dateFK = new DateTime(); ;
            if (Convert.ToDateTime(DateFrom).Day <25)
            {
                dateFK = Convert.ToDateTime(Convert.ToDateTime(DateFrom).ToString("yyyy-MM-10"));
            }
            else
                dateFK = Convert.ToDateTime(Convert.ToDateTime(DateFrom).ToString("yyyy-MM-25"));

            if (dateFK.DayOfWeek == DayOfWeek.Saturday || dateFK.DayOfWeek == DayOfWeek.Sunday)
            {
                dateFK = dateFK.AddDays(2);
            }

            if (dateFK.DayOfWeek == DayOfWeek.Sunday)
            {
                dateFK = dateFK.AddDays(1);
            }
            return dateFK;
           // return dateFK.ToString("yyyy年MM月dd日");

        }

        private DateTime GetRZCZDate()
        {
            DateTime dateFK = GetRZFKDate();
            if (dateFK.Day < 25)
                return Convert.ToDateTime( dateFK.ToString("yyyy-MM-") + "25");
            else
                return  Convert.ToDateTime( dateFK.AddMonths(1).ToString("yyyy-MM-") + "10");
        }

        //protected override string GetXlsFileName(string oldFileName)
        //{
        //    //return base.GetXlsFileName(oldFileName);

        //    return string.Format("{0}{1}.xls", DateTime.Now.ToString("yyyy年MM月"), "融资租赁款结算确认书汇总表").Trim();
        //}
    }
}